﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Data.Common;
using Data.Enum;
using Data.Model;

namespace DataAccess.AccountManage
{
    /// <summary>
    /// 用户(增加或删除操作记得增加或删除相关表的数目)
    /// </summary>
    public class Account : DbBase, IAccount
    {
        /// <summary>
        /// 添加或创建账户,如果account有id就是修改，没有id就是新增
        /// </summary>
        /// <param name="account"></param>
        /// <returns></returns>
        public long CreateUpdateAccount(jsh_account account)
        {
            long result = 0;

            try
            {
                SqlCommand command = GetSqlCommand();
                if (command != null && account != null)
                {
                    if (account.id <= 0)
                    {
                        string addSql = string.Format("insert into jsh_account([cpuid],[cpuno],[username],[sex],[cardtype],[status],[nation],[zzlx],[zzhm],[fzdw],[sdate],[edate],[telephone],[mobile],[addr1],[addr2],[khdate],[deposit],[balance],[credit],[password],[operator],[operatorid],[closedate],[memo],[photo]) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}',getdate(),{16},{17},{18},'{19}','{20}',{21},@closedate,'{22}',@photo) select @@IDENTITY as returnId",
                            account.cpuid.Replace("\0", ""), account.cpuno.Replace("\0", ""), account.username.Replace("\0", ""), account.sex.Replace("\0", ""), account.cardtype.Replace("\0", ""), account.status.Replace("\0", ""), account.nation.Replace("\0", ""), account.zzlx.Replace("\0", ""), account.zzhm.Replace("\0", ""), account.fzdw.Replace("\0", ""), account.sdate.Replace("\0", ""), account.edate.Replace("\0", ""), account.telephone.Replace("\0", ""), account.mobile.Replace("\0", ""), account.addr1.Replace("\0", ""), account.addr2.Replace("\0", ""), account.deposit, account.balance, account.credit, account.password.Replace("\0", ""), account.operator1.Replace("\0", ""), account.operatorid, account.memo.Replace("\0", ""));
                        command.CommandText = addSql;
                        var pa1 = new SqlParameter("@closedate", SqlDbType.DateTime) { Value = account.closedate };
                        var pa2 = new SqlParameter("@photo", SqlDbType.Image) { Value = account.photo };
                        command.Parameters.Add(pa1);
                        command.Parameters.Add(pa2);
                        result = Convert.ToInt64(command.ExecuteScalar());
                    }
                    else
                    {
                        var updateSql = string.Format("UPDATE jsh_account SET [cpuid]='{1}',[cpuno]='{2}',[username]='{3}',[sex]='{4}',[cardtype]='{5}',[status]='{6}',[nation]='{7}',[zzlx]='{8}',[zzhm]='{9}',[fzdw]='{10}',[sdate]='{11}',[edate]='{12}',[telephone]='{13}',[mobile]='{14}',[addr1]='{15}',[addr2]='{16}',[deposit]={17},[balance]={18},[credit]={19},[password]='{20}',[operator]='{21}',[operatorid]={22},[closedate]=@closedate,[memo]='{23}',[photo]=@photo where [id]={0}",
                            account.id, account.cpuid.Replace("\0", ""), account.cpuno.Replace("\0", ""), account.username.Replace("\0", ""), account.sex.Replace("\0", ""), account.cardtype.Replace("\0", ""), account.status.Replace("\0", ""), account.nation.Replace("\0", ""), account.zzlx.Replace("\0", ""), account.zzhm.Replace("\0", ""), account.fzdw.Replace("\0", ""), account.sdate.Replace("\0", ""), account.edate.Replace("\0", ""), account.telephone.Replace("\0", ""), account.mobile.Replace("\0", ""), account.addr1.Replace("\0", ""), account.addr2.Replace("\0", ""), account.deposit, account.balance, account.credit, account.password.Replace("\0", ""), account.operator1.Replace("\0", ""), account.operatorid, account.memo.Replace("\0", ""));
                        var pa1 = new SqlParameter("@closedate", SqlDbType.DateTime) { Value = account.closedate };
                        var pa2 = new SqlParameter("@photo", SqlDbType.Image) { Value = account.photo };
                        command.Parameters.Add(pa1);
                        command.Parameters.Add(pa2);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception)
            {
                //CloseConn();
            }
            return result;
        }

        /// <summary>
        /// 更具标识获取记录
        /// </summary>
        /// <param name="id">账户的id</param>
        /// <returns></returns>
        public jsh_account GetAccount(long id)
        {
            jsh_account result = null;

            string sql = string.Format("select * from jsh_account where id={0}", id);
            DataSet dataSet = ExcuteSqlDataSet(sql);
            if (dataSet != null && dataSet.Tables[0].Rows.Count == 1)
            {
                result = new jsh_account(dataSet.Tables[0].Rows[0]);
                if (!result.TranByConstructFlag)
                {
                    result = null;
                }
            }
            //CloseConn();
            return result;
        }

        /// <summary>
        /// 开户
        /// </summary>
        /// <param name="jshAccount">账户基本信息</param>
        /// <param name="initialMoney">cpu卡初始金额</param>
        /// <param name="mountMoney">充值金额</param>
        /// <param name="receiptMoney">押金</param>
        /// <param name="operateaddress">操作点地址</param>
        /// <param name="operateaddressid">操作点id</param>
        /// <param name="operatepsamid">操作点psamid</param>
        /// <returns></returns>
        public ResultInfo<bool> OpenAccount(jsh_account jshAccount, Decimal initialMoney, Decimal mountMoney, Decimal receiptMoney, string operateaddress, Int64 operateaddressid, string operatepsamid)
        {
            var result = new ResultInfo<bool> { ResultNumber = ResultEnum.Error, Obj = false };
            try
            {
                if (jshAccount != null && initialMoney >= 0 && mountMoney >= 0 && receiptMoney >= 0)
                {
                    SqlCommand command = GetProCommand();
                    command.CommandText = "Create_OpenAccount";
                    command.Parameters.Add("@cpuid", SqlDbType.VarChar, 50).Value = jshAccount.cpuid;
                    command.Parameters.Add("@cpuno", SqlDbType.VarChar, 50).Value = jshAccount.cpuno;
                    command.Parameters.Add("@username", SqlDbType.VarChar, 50).Value = jshAccount.username;
                    command.Parameters.Add("@sex", SqlDbType.Char, 2).Value = jshAccount.sex;
                    command.Parameters.Add("@cardtype", SqlDbType.VarChar, 50).Value = jshAccount.cardtype;
                    command.Parameters.Add("@status", SqlDbType.VarChar, 50).Value = jshAccount.status;
                    command.Parameters.Add("@nation", SqlDbType.VarChar, 50).Value = jshAccount.nation;
                    command.Parameters.Add("@zzlx", SqlDbType.VarChar, 10).Value = jshAccount.zzlx;
                    command.Parameters.Add("@zzhm", SqlDbType.VarChar, 50).Value = jshAccount.zzhm;
                    command.Parameters.Add("@fzdw", SqlDbType.VarChar, 50).Value = jshAccount.fzdw;
                    command.Parameters.Add("@sdate", SqlDbType.VarChar, 50).Value = jshAccount.sdate;
                    command.Parameters.Add("@edate", SqlDbType.VarChar, 50).Value = jshAccount.edate;
                    command.Parameters.Add("@telephone", SqlDbType.VarChar, 20).Value = jshAccount.telephone;
                    command.Parameters.Add("@mobile", SqlDbType.VarChar, 10).Value = jshAccount.mobile;
                    command.Parameters.Add("@addr1", SqlDbType.VarChar, 255).Value = jshAccount.addr1;
                    command.Parameters.Add("@addr2", SqlDbType.VarChar, 255).Value = jshAccount.addr2;
                    command.Parameters.Add("@khdate", SqlDbType.VarChar, 50).Value = jshAccount.khdate;
                    command.Parameters.Add("@validate", SqlDbType.VarChar, 50).Value = jshAccount.validate;
                    command.Parameters.Add("@deposit", SqlDbType.Money).Value = jshAccount.deposit;
                    command.Parameters.Add("@balance", SqlDbType.Money).Value = jshAccount.balance;
                    command.Parameters.Add("@credit", SqlDbType.Float).Value = jshAccount.credit;
                    command.Parameters.Add("@password", SqlDbType.VarChar, 30).Value = jshAccount.password;
                    command.Parameters.Add("@ver", SqlDbType.VarChar, 10).Value = jshAccount.ver;
                    command.Parameters.Add("@type", SqlDbType.VarChar, 30).Value = jshAccount.type;
                    command.Parameters.Add("@operator", SqlDbType.VarChar, 50).Value = jshAccount.operator1;
                    command.Parameters.Add("@operatorid", SqlDbType.BigInt).Value = jshAccount.operatorid;
                    command.Parameters.Add("@closedate", SqlDbType.DateTime).Value = jshAccount.closedate <
                                                                                     new DateTime(2000, 1, 1)
                                                                                         ? DateTime.Now
                                                                                         : jshAccount.closedate;
                    command.Parameters.Add("@memo", SqlDbType.VarChar, 50).Value = jshAccount.memo;
                    command.Parameters.Add("@photo", SqlDbType.Image).Value = jshAccount.photo;
                    command.Parameters.Add("@initialMoney", SqlDbType.Decimal).Value = initialMoney;
                    command.Parameters.Add("@amountMoney", SqlDbType.Decimal).Value = mountMoney;
                    command.Parameters.Add("@receiptMoney", SqlDbType.Decimal).Value = receiptMoney;
                    command.Parameters.Add("@operateaddress", SqlDbType.VarChar, 50).Value = operateaddress;
                    command.Parameters.Add("@operateaddressid", SqlDbType.BigInt).Value = operateaddressid;
                    command.Parameters.Add("@operatepsamid", SqlDbType.VarChar, 50).Value = operatepsamid;
                    command.Parameters.Add("@returnFlag", SqlDbType.Int).Direction = ParameterDirection.Output;
                    command.ExecuteNonQuery();
                    int returnFlag = Convert.ToInt32(command.Parameters["@returnFlag"].Value);
                    if (returnFlag == 0)
                    {
                        result.ResultNumber = ResultEnum.Success;
                        result.Obj = true;
                    }
                    else
                    {
                        result.ResultNumber = ResultEnum.Error;
                        result.Obj = false;
                        result.Describe = "执行开户存储过程出现错误";
                    }
                }
                else
                {
                    result.ResultNumber = ResultEnum.Error;
                    result.Obj = false;
                    result.Describe = "参数为空或小于0，请检查";
                }
            }
            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Obj = false;
                result.Describe = e.Message;
            }
            return result;
        }

        /// <summary>
        /// 获取用户列表
        /// </summary>
        /// <param name="pageIndex">页面标号</param>
        /// <param name="pageSize">每页数量</param>
        /// <param name="userName">用户名</param>
        /// <param name="idNo">身份证号码</param>
        /// <param name="cpuno">可以是cpu卡id或者是硬刷编号</param>
        /// <returns></returns>
        public ResultInfo<PagingDataSet<jsh_account>> GetAccounts(int pageIndex, int pageSize, string userName, string idNo, string cpuno)
        {
            var result = new ResultInfo<PagingDataSet<jsh_account>> { ResultNumber = ResultEnum.Error };
            if (!string.IsNullOrEmpty(userName) || !string.IsNullOrEmpty(idNo) || !string.IsNullOrEmpty(cpuno))
            {
                try
                {
                    using (SqlCommand myCommand = GetProCommand())
                    {
                        myCommand.CommandText = "Common_GetPagingRecords";
                        var fromClause = new StringBuilder();
                        fromClause.AppendFormat("jsh_account R with (nolock)");

                        var whereClause = new StringBuilder();
                        if (!string.IsNullOrEmpty(userName))
                        {
                            whereClause.Append(string.Format(" and R.username = '{0}' ", userName));
                        }
                        if (!string.IsNullOrEmpty(idNo))
                        {
                            whereClause.Append(string.Format(" and R.zzhm='{0}' ", idNo));
                        }
                        if (!string.IsNullOrEmpty(cpuno))
                        {
                            whereClause.Append(string.Format(" and (R.cpuid='{0}' or R.cpuno='{0}') ", cpuno));
                        }
                        if (whereClause.Length > 3)
                        {
                            whereClause.Remove(0, 5);
                        }

                        myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex - 1;
                        myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
                        myCommand.Parameters.Add("@FromClause", SqlDbType.VarChar, 1024).Value = fromClause.ToString();
                        myCommand.Parameters.Add("@SelectFields", SqlDbType.VarChar, 512).Value = "R.*";
                        myCommand.Parameters.Add("@WhereClause", SqlDbType.NVarChar, 4000).Value = whereClause.ToString();
                        myCommand.Parameters.Add("@SortField", SqlDbType.VarChar, 256).Value = "R.id";
                        myCommand.Parameters.Add("@SortFieldIsDesc", SqlDbType.Bit).Value = true;
                        myCommand.Parameters.Add("@ReturnRecordCount", SqlDbType.Bit).Value = true;
                        myCommand.Parameters.Add("@MaxRecords", SqlDbType.Int).Value = int.MaxValue;
                        myCommand.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;
                        myCommand.Parameters.Add("@ReverseOrder", SqlDbType.Bit).Direction = ParameterDirection.Output;

                        var dataSet = new DataSet();
                        var da = new SqlDataAdapter(myCommand);
                        da.Fill(dataSet);

                        var list = dataSet.Tables[0].Rows.Cast<DataRow>().Select(dataRow => new jsh_account(dataRow)).Where(jshAccount => jshAccount.TranByConstructFlag).ToList();

                        var totalRecords = Convert.ToInt32(myCommand.Parameters["@TotalRecords"].Value);
                        var reverseOrder = Convert.ToBoolean(myCommand.Parameters["@ReverseOrder"].Value);

                        if (reverseOrder)
                            list.Reverse();

                        result.ResultNumber = ResultEnum.Success;
                        result.Obj = new PagingDataSet<jsh_account> { PageIndex = pageIndex, PageSize = pageSize, Records = list, TotalRecords = totalRecords };
                    }
                }
                catch (Exception e)
                {
                    result.ResultNumber = ResultEnum.Error;
                    result.Describe = e.Message;
                }
            }
            else
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = "至少输入一项查询条件：用户名、证件号、用户卡号";
            }
            return result;
        }


        /// <summary>
        /// 根据cpuid,或者是cpuno获取记录
        /// </summary>
        /// <param name="cpuid">cpuid或者是cpuno</param>
        /// <returns></returns>
        public ResultInfo<jsh_account> GetAccount(string cpuid)
        {
            var result = new ResultInfo<jsh_account>();
            try
            {
                string sql = string.Format("select * from jsh_account where cpuid='{0}' or cpuno='{0}'", cpuid);
                DataSet dataSet = ExcuteSqlDataSet(sql);
                if (dataSet != null && dataSet.Tables[0].Rows.Count == 1)
                {
                    var temp = new jsh_account(dataSet.Tables[0].Rows[0]);
                    if (temp.TranByConstructFlag)
                    {
                        result.ResultNumber = ResultEnum.Success;
                        result.Obj = temp;
                    }
                    else
                    {
                        result.ResultNumber = ResultEnum.Error;
                        result.Describe = "没查到指定的用户";
                    }
                }
                else
                {
                    result.ResultNumber = ResultEnum.Error;
                    result.Describe = "没查到指定的用户";
                }
                //CloseConn();
            }
            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }
            return result;
        }

        /// <summary>
        /// 用证件号码获取用户
        /// </summary>
        /// <param name="idCardNo">身份证号</param>
        /// <returns></returns>
        public ResultInfo<jsh_account> GetAccountWithIdCard(string idCardNo)
        {
            var result = new ResultInfo<jsh_account>();
            try
            {
                string sql = string.Format("select * from jsh_account where zzhm='{0}'", idCardNo);
                DataSet dataSet = ExcuteSqlDataSet(sql);
                if (dataSet != null && dataSet.Tables[0].Rows.Count == 1)
                {
                    var temp = new jsh_account(dataSet.Tables[0].Rows[0]);
                    if (temp.TranByConstructFlag)
                    {
                        result.ResultNumber = ResultEnum.Success;
                        result.Obj = temp;
                    }
                    else
                    {
                        result.ResultNumber = ResultEnum.Error;
                        result.Describe = "没查到指定的用户";
                    }
                }
                else
                {
                    result.ResultNumber = ResultEnum.Error;
                    result.Describe = "没查到指定的用户";
                }
                //CloseConn();
            }
            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }
            return result;
        }

        /// <summary>
        /// 更新账号密码
        /// </summary>
        /// <param name="accountid">账户的id</param>
        /// <param name="password">密码</param>
        /// <returns></returns>
        public ResultInfo<bool> UpdateAccountPassword(long accountid, string password)
        {
            var result = new ResultInfo<bool>();
            try
            {
                string sql = string.Format("UPDATE jsh_account SET [password]='{0}' where [id]={1}", password, accountid);

                int temp = ExcuteSql(sql);
                if (temp > 0)
                {
                    result.ResultNumber = ResultEnum.Success;
                    result.Obj = true;
                }
                else
                {
                    result.ResultNumber = ResultEnum.Error;
                    result.Describe = "更新失败";
                }
                //CloseConn();
            }
            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }
            return result;
        }

        /// <summary>
        /// 更新账号状态
        /// </summary>
        /// <param name="accountid">账户id</param>
        /// <param name="status">账户的状态</param>
        /// <returns></returns>
        public ResultInfo<bool> UpdateAccountStatus(long accountid, string status)
        {
            var result = new ResultInfo<bool>();
            try
            {
                string sql = string.Format("UPDATE jsh_account SET [status]='{0}' where [id]={1}", status, accountid);

                int temp = ExcuteSql(sql);
                if (temp > 0)
                {
                    result.ResultNumber = ResultEnum.Success;
                    result.Obj = true;
                }
                else
                {
                    result.ResultNumber = ResultEnum.Error;
                    result.Describe = "更新失败";
                }
                //CloseConn();
            }
            catch (Exception e)
            {
                result.ResultNumber = ResultEnum.Error;
                result.Describe = e.Message;
            }
            return result;
        }

    }
}
